package com.yuda.afterexamscore.controller;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.ZipUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.yuda.afterexamscore.vo.GaigeDataByHaiXiao;
import com.yuda.afterexamscore.vo.JiaShiDataByHaiXiaoVo;
import com.yuda.afterexamscore.vo.RedisErr;
import com.yuda.common.core.web.domain.AjaxResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.util.*;
import java.util.concurrent.*;
import java.util.stream.Collectors;

/**
 * 从海笑转过来，处理南昊excel表，进行数据再处理
 *
 * @author fmx
 * @date 2021年7月15日
 */
@RestController
@RequestMapping("/dataReprocessing")
@Api("从海笑转接来，处理南昊excel表，进行数据再处理")
@RefreshScope
public class DataReprocessingByHaiXiaoController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private RedisTemplate redisTemplate;

    private static Integer process = 0;

    @Value("${examUUID}")
    public String examUUID;

//    public String sourceFliePath = "D:\\黄老板数据\\source\\source\\二卷区处理_正式\\总分";
    public String sourceFliePath = "/data";

//    public String outFliePath = "D:\\黄老板数据\\source\\source\\二卷区处理_正式\\总分";
    public String outFliePath = "/data/out";
    private static Queue<Map<String, Object>> importScoreExcelQueue = new ConcurrentLinkedQueue<>();

    private static Logger log = LoggerFactory.getLogger(DataReprocessingByHaiXiaoController.class);

    //todo 定义两个阈值 看黄老板 学校为3 省份数量为90
    public static Long schoolThreshold = 3l;
    public static Long provinceThreshold = 90l;

    @PostMapping("uploadHaiXiaoFolder")
    @ApiOperation("上传压缩包,文件夹名叫source里面是：一卷区处理_正式等三个文件夹的压缩包")
    public void uploadHaiXiaoFolder(MultipartFile file) throws IOException {
        DataReprocessingByHaiXiaoController.process = 5;
        //把上一次的删除
        boolean exist = FileUtil.exist(sourceFliePath+File.separator+examUUID);
        if (exist){
            FileUtil.del(sourceFliePath+File.separator+examUUID);
        }
        //将之前的错误都删除
        redisTemplate.delete(examUUID+"DataReprocessingByHaiXiaoErr");
        OutputStream os = null;
        InputStream inputStream = null;
        String fileName = null;
        try {
            inputStream = file.getInputStream();
            fileName = file.getOriginalFilename();
            log.info("文件名为:"+fileName);
            if (!fileName.equals("source.zip")){
                insertHaiXiaoErr("文件名错误");
                return;
            }
        } catch (IOException e) {
            insertHaiXiaoErr("上传失败，请查看日志");
            e.printStackTrace();
        }
        try {
            String path = sourceFliePath+File.separator+examUUID;
            log.info("path:"+path);
            // 2、保存到临时文件
            // 1K的数据缓冲
            byte[] bs = new byte[1024];
            // 读取到的数据长度
            int len;
            // 输出的文件流保存到本地文件
            File tempFile = new File(path);
            if (!tempFile.exists()) {
                tempFile.mkdirs();
            }
            os = new FileOutputStream(tempFile.getPath() + File.separator + fileName);
            // 开始读取
            while ((len = inputStream.read(bs)) != -1) {
                os.write(bs, 0, len);
            }
            //上传结束,开始解压
//            ZipUtil.unzip(tempFile.getPath() + File.separator + fileName, Charset.forName("GBK"));
            ZipUtil.unzip(tempFile.getPath() + File.separator + fileName, Charset.forName("GBK"));
            //建表，zf_qgj1_zonghe_haixiao
            jdbcTemplate.execute(createSql);
            jdbcTemplate.update("truncate table zf_qgj1_zonghe_haixiao");
            //将数据插入表里
//            ExcelReader reader = ExcelUtil.getReader("D:\\黄老板数据\\THUSSAT20210708BAOMING\\source\\source\\改革区处理_正式\\总分\\全国卷综合各省分数总表_.xlsx");
            File f = new File(sourceFliePath + File.separator + examUUID + "/source/source/改革区处理_正式/总分/全国卷综合各省分数总表_.xlsx");
            if (!f.exists()){
                FileUtil.del(sourceFliePath + File.separator + examUUID);
                insertHaiXiaoErr("文件内容不匹配，请核对");
                return;
            }
            log.info("文件路径"+ f.getAbsolutePath());
            ExcelReader reader = ExcelUtil.getReader(sourceFliePath+File.separator+examUUID+"/source/source/改革区处理_正式/总分/全国卷综合各省分数总表_.xlsx");
            List<Map<String, Object>> maps = reader.readAll();
            importScoreExcelQueue.addAll(maps);
            DataReprocessingByHaiXiaoController.process = 20;
            //开线程，多线程处理分数
            int threadNum = (int) Math.ceil(maps.size()/100) + 1;
            if (threadNum>=1){
                CountDownLatch latch = new CountDownLatch(threadNum);
                ExecutorService executor = Executors.newFixedThreadPool(100);
                for (int i = 0; i < threadNum; i++) { // 开threadNum个线程
                    Runnable task = new DataReprocessingByHaiXiaoController.MultithreadingOfWorkData(latch);
                    // 执行
                    executor.execute(task);
                }
                latch.await(); //
                executor.shutdown();//关闭线程池
            }
            log.info("数据插入success!!! ");
            //开始修改表数据
            jdbcTemplate.update("UPDATE zf_qgj1_zonghe_haixiao \n" +
                    "set sum_score = (IFNULL(yuwen,0)+IFNULL(shuxue,0)+IFNULL(yingyu,0)) ,\n" +
                    "xuanke_score = (IFNULL(wuli,0)+IFNULL(huaxue,0)+IFNULL(shengwu,0)+IFNULL(zhengzhi,0)+IFNULL(lishi,0)+IFNULL(dili,0))");
            log.info("修改表数据success!!! ");
            //语数英全国排名更新
            jdbcTemplate.update(updateSumPercentageG);
            log.info("语数英全国排名更新success!!! ");
            DataReprocessingByHaiXiaoController.process = 40;
            //语数英省排名更新
            List<String> province = jdbcTemplate.queryForList("SELECT DISTINCT province from zf_qgj1_zonghe_haixiao", String.class);
            for (String p : province) {
                //省份数量少&&总分不为0&&学校数量太少显示为-
                Long allCount = jdbcTemplate.queryForObject("SELECT count(1) c from zf_qgj1_zonghe_haixiao where province = ?",new Object[]{p}, Long.class);
                Long schoolCount = jdbcTemplate.queryForObject("SELECT count(1) c from\n" +
                        "(SELECT DISTINCT school_name from zf_qgj1_zonghe_haixiao where province = ?) a",new Object[]{p}, Long.class);
                if (allCount<=provinceThreshold || schoolCount<=schoolThreshold){
                    jdbcTemplate.update("UPDATE zf_qgj1_zonghe_haixiao \n" +
                            "set sum_percentage_s = '-',xuanke_percentage_s = '-' \n" +
                            "where sum_score != 0 and province = ?",new Object[]{p});
                }else {
                    jdbcTemplate.update(updateSumPercentageS,new Object[]{p,p,p});
                }
            }
            log.info("语数英省排名更新success!!! ");
            //语数英学校排名更新
            List<String> school = jdbcTemplate.queryForList("SELECT DISTINCT school_name from zf_qgj1_zonghe_haixiao", String.class);
            for (String s : school) {
                jdbcTemplate.update(updateSumRankX,new Object[]{s});
            }
            log.info("语数英学校排名更新success!!! ");
            DataReprocessingByHaiXiaoController.process = 60;
            //选课数据改变
            List<String> categorys = jdbcTemplate.queryForList("SELECT DISTINCT category from zf_qgj1_zonghe_haixiao", String.class);
            for (String category : categorys) {
                //查询有多少个 少于90个不赋值
                Long count = jdbcTemplate.queryForObject("SELECT count(1) c from zf_qgj1_zonghe_haixiao where category = ?", new Object[]{category}, Long.class);
                if (count <= provinceThreshold){
                    jdbcTemplate.update("UPDATE zf_qgj1_zonghe_haixiao set xuanke_percentage_g = '-' where xuanke_score != 0 and category = ?", new Object[]{category});
                }else {
                    jdbcTemplate.update(updateXuankePercentageG,new Object[]{category,category,category});
                }
                //省份和选课大于90才可以出百分比
                for (String p : province) {
                    Long countS = jdbcTemplate.queryForObject("SELECT count(1) c from zf_qgj1_zonghe_haixiao where category = ? and province = ?", new Object[]{category, p}, Long.class);
                    if (countS <= provinceThreshold){
                        jdbcTemplate.update("UPDATE zf_qgj1_zonghe_haixiao set xuanke_percentage_s = '-' where xuanke_score != 0 and category = ? and province = ?", new Object[]{category,p});
                    }else {
                        jdbcTemplate.update(updateXuankePercentageS,new Object[]{category,p,category,p,category,p});
                    }
                }
                for (String s : school) {
                    jdbcTemplate.update(updateXuankeRankX,new Object[]{s,category});
                }
            }
            DataReprocessingByHaiXiaoController.process = 90;
            //最后把所有大于99的数据换成99+
            jdbcTemplate.update("update zf_qgj1_zonghe_haixiao set sum_percentage_g = '99+' where sum_percentage_g != '-' and  sum_percentage_g+0 > 99");
            jdbcTemplate.update("update zf_qgj1_zonghe_haixiao set sum_percentage_s = '99+' where sum_percentage_s != '-' and  sum_percentage_s+0 > 99");
            jdbcTemplate.update("update zf_qgj1_zonghe_haixiao set xuanke_percentage_g = '99+' where xuanke_percentage_g != '-' and  xuanke_percentage_g+0 > 99");
            jdbcTemplate.update("update zf_qgj1_zonghe_haixiao set xuanke_percentage_s = '99+' where xuanke_percentage_s != '-' and   xuanke_percentage_s+0 > 99");
            log.info("选课更新success!!! ");
            DataReprocessingByHaiXiaoController.process = 100;
        }catch (Exception e) {
            RedisErr redisErr = new RedisErr();
            redisErr.setMsg("上传失败，请查看日志");
            redisErr.setCurrentTime(DateUtil.now());
            JSON parse = JSONUtil.parse(redisErr);
            redisTemplate.opsForSet().add(examUUID+"DataReprocessingByHaiXiaoErr",parse);
            e.printStackTrace();
        } finally {
            // 完毕，关闭所有链接
            try {
                os.close();
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @ApiOperation("判断是否存在压缩包")
    @GetMapping("/getExcelZipExsit")
    public AjaxResult getExcelZipExsit() throws IOException {
        boolean exist = FileUtil.exist(outFliePath + "/给清华的数据模板/" + examUUID + ".zip");
        return AjaxResult.success(exist);
    }

    @ApiOperation("返回压缩包")
    @GetMapping("/getExcelZip")
    public void getExcelZip(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //下载
        System.out.println("压缩完毕,正在下载");
        String filename = examUUID + ".zip";
        //File.separator代表了当前系统的文件分割符
        //linux使用/分割符会报错找不到文件,window系统下/和\都可以作为文件路径
//        File zipFile = new File("D:" + File.separator + examUUID + "Excel.zip");
        File zipFile = new File(outFliePath + "/给清华的数据模板/" + examUUID + ".zip");
        FileInputStream in = new FileInputStream(zipFile);
        String agent = request.getHeader("user-agent");
        if (agent.contains("FireFox")) {
            filename = new String(filename.getBytes("UTF-8"), "iso-8859-1");
        } else {
            filename = URLEncoder.encode(filename, "UTF-8");
        }
        // 设置下载文件的mineType，告诉浏览器下载文件类型
        String mineType = request.getServletContext().getMimeType(filename);
        response.setContentType(mineType);
        // 设置一个响应头，无论是否被浏览器解析，都下载
        response.setHeader("Content-disposition", "attachment; filename=" + filename);
        // 将要下载的文件内容通过输出流写到浏览器
        OutputStream out = response.getOutputStream();
        int len = 0;
        byte[] buffer = new byte[1024];
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);
        }
        out.close();
        in.close();
    }

    @GetMapping("dealToQingHuaData")
    public void dealToQingHuaData(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //先创建所有省份表格
        //先处理非改革总分表
        judgmentiSExit(sourceFliePath+File.separator+examUUID+"/source/source/二卷区处理_正式/总分/全国卷理科各省分数总表.xlsx");
        judgmentiSExit(sourceFliePath+File.separator+examUUID+"/source/source/二卷区处理_正式/总分/全国卷文科各省分数总表.xlsx");
        judgmentiSExit(sourceFliePath+File.separator+examUUID+"/source/source/一卷区处理_正式/总分/全国卷理科各省分数总表.xlsx");
        judgmentiSExit(sourceFliePath+File.separator+examUUID+"/source/source/一卷区处理_正式/总分/全国卷文科各省分数总表.xlsx");
//        judgmentiSExit("D:\\黄老板数据\\THUSSAT20210708BAOMING\\source\\source\\二卷区处理_正式\\总分\\全国卷文科各省分数总表.xlsx");
//        judgmentiSExit("D:\\黄老板数据\\THUSSAT20210708BAOMING\\source\\source\\一卷区处理_正式\\总分\\全国卷理科各省分数总表.xlsx");
//        judgmentiSExit("D:\\黄老板数据\\THUSSAT20210708BAOMING\\source\\source\\一卷区处理_正式\\总分\\全国卷文科各省分数总表.xlsx");
        //处理改革的
        List<String> province = jdbcTemplate.queryForList("SELECT DISTINCT province from zf_qgj1_zonghe_haixiao", String.class);
        for (String p : province) {
            ExcelWriter writer = ExcelUtil.getWriter(outFliePath+"/给清华的数据模板/" + examUUID + "/诊断性测试成绩表_" + p +".xlsx","汇总");
            List<GaigeDataByHaiXiao> datas = jdbcTemplate.query("select * from zf_qgj1_zonghe_haixiao where province = ?", new Object[]{p}, new BeanPropertyRowMapper<>(GaigeDataByHaiXiao.class));
            setHeader(writer);
            writer.write(datas, true);
            writer.close();
        }
        //处理加试
        List<GaigeDataByHaiXiao> provinceAll = jdbcTemplate.query("select DISTINCT b.name ,b.id province from dp_total_score a left join sparta_province b on a.province_id = b.id ", new BeanPropertyRowMapper<>(GaigeDataByHaiXiao.class));
        for (GaigeDataByHaiXiao p : provinceAll) {
            List<JiaShiDataByHaiXiaoVo> fuJia = jdbcTemplate.query(queryJiaShiData, new Object[]{p.getProvince()}, new BeanPropertyRowMapper<>(JiaShiDataByHaiXiaoVo.class));
            ExcelWriter writer = ExcelUtil.getWriter(outFliePath+"/给清华的数据模板/" + examUUID + "/诊断性测试成绩表_" + p.getName() +".xlsx","加试");
            setHeader(writer);
            writer.write(fuJia, true);
            writer.close();
        }
        ZipUtil.zip(outFliePath+"/给清华的数据模板/" +examUUID);
        log.info("success!!!!!");
        //返回
        String filename = examUUID + ".zip";
        //File.separator代表了当前系统的文件分割符
        //linux使用/分割符会报错找不到文件,window系统下/和\都可以作为文件路径
//        File zipFile = new File("D:" + File.separator + examUUID + "Excel.zip");
        File zipFile = new File(outFliePath + "/给清华的数据模板/" + examUUID + ".zip");
        FileInputStream in = new FileInputStream(zipFile);
        String agent = request.getHeader("user-agent");
        if (agent.contains("FireFox")) {
            filename = new String(filename.getBytes("UTF-8"), "iso-8859-1");
        } else {
            filename = URLEncoder.encode(filename, "UTF-8");
        }
        // 设置下载文件的mineType，告诉浏览器下载文件类型
        String mineType = request.getServletContext().getMimeType(filename);
        response.setContentType(mineType);
        // 设置一个响应头，无论是否被浏览器解析，都下载
        response.setHeader("Content-disposition", "attachment; filename=" + filename);
        // 将要下载的文件内容通过输出流写到浏览器
        OutputStream out = response.getOutputStream();
        int len = 0;
        byte[] buffer = new byte[1024];
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);
        }
        out.close();
        in.close();
    }

    @ApiOperation("获取上传进度")
    @GetMapping("/queryDataReprocessingByHaiXiaoProcess")
//    @PreAuthorize(hasRole = "admin")
    public AjaxResult queryDataReprocessingByHaiXiaoProcess() {
        return AjaxResult.success(DataReprocessingByHaiXiaoController.process);
    }

    /**
     * 返回TsinghuaFormalAnswerErr
     *
     */
    @GetMapping("/queryDataReprocessingByHaiXiaoErr")
    @ApiOperation("查询错误,有错误则返回错误列表,没错误则不返回data")
//    @PreAuthorize(hasRole = "admin")
    public AjaxResult queryDataReprocessingByHaiXiaoErr() {
        Boolean hasKey = redisTemplate.hasKey(examUUID + "DataReprocessingByHaiXiaoErr");
        if (hasKey){
            Set members = redisTemplate.opsForSet().members(examUUID + "DataReprocessingByHaiXiaoErr");
            return AjaxResult.success(members);
        }else {
            return AjaxResult.success();
        }
    }

    public void setHeader(ExcelWriter writer){
        writer.addHeaderAlias("province", "省份");
        writer.addHeaderAlias("school_name", "学校");
        writer.addHeaderAlias("banji", "班级");
        writer.addHeaderAlias("zkz_num", "考号");
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("type", "类别");
        writer.addHeaderAlias("category", "选考科目");
        writer.addHeaderAlias("yuwen", "语文");
        writer.addHeaderAlias("shuxue", "数学");
        writer.addHeaderAlias("yingyu", "英语");
        writer.addHeaderAlias("wuli", "物理");
        writer.addHeaderAlias("huaxue", "化学");
        writer.addHeaderAlias("shengwu", "生物");
        writer.addHeaderAlias("zhengzhi", "政治");
        writer.addHeaderAlias("lishi", "历史");
        writer.addHeaderAlias("dili", "地理");
        writer.addHeaderAlias("sum_score", "语数英总分");
        writer.addHeaderAlias("xuanke_score", "选科总分");
        writer.addHeaderAlias("sum_percentage_g", "语数英总分改革区内百分比排名");
        writer.addHeaderAlias("sum_percentage_s", "语数英总分本省百分比排名");
        writer.addHeaderAlias("sum_rank_x", "语数英总分本校排名");
        writer.addHeaderAlias("xuanke_percentage_g", "改革区内同科目选科总分百分比排名");
        writer.addHeaderAlias("xuanke_percentage_s", "本省同科目选科总分百分比排名");
        writer.addHeaderAlias("xuanke_rank_x", "同科目组选科总分本校排名");
        writer.addHeaderAlias("scoreOne", "附加科目1成绩");
        writer.addHeaderAlias("rankOneG", "附加科目1全国百分比排名");
        writer.addHeaderAlias("rankOneS", "附加科目1省百分比排名");
        writer.addHeaderAlias("scoreTwo", "附加科目2成绩");
        writer.addHeaderAlias("rankTwoG", "附加科目2全国百分比排名");
        writer.addHeaderAlias("rankTwoS", "附加科目2省百分比排名");
        writer.setOnlyAlias(true);
    }


    public void createToTsingHuaExcelFeiGaiGe(String filePath){
        ExcelReader reader = ExcelUtil.getReader(filePath);
        List<Map<String, Object>> maps = reader.readAll();
        //提取出有多少省份,创建相应excel
        Set<String> provinces = new HashSet<>();
        maps.stream().forEach(m -> provinces.add(m.get("省份").toString()));
        System.out.println(provinces);
        ExcelWriter writer = null;
        for (String province : provinces) {
            if (filePath.contains("理科")){//outFliePath+"/给清华的数据模板/" + examUUID + "/诊断性测试成绩表_" + p.getName() +".xlsx","加试"
                writer = ExcelUtil.getWriter(outFliePath+"/给清华的数据模板/" + examUUID + "/诊断性测试成绩表_" + province+".xlsx","理科汇总");
            }else {
                writer = ExcelUtil.getWriter(outFliePath+"/给清华的数据模板/" + examUUID + "/诊断性测试成绩表_" + province+".xlsx","文科汇总");
            }
            List<Map<String, Object>> objs = maps.stream().filter(m -> m.get("省份").toString().equals(province)).collect(Collectors.toList());
            //学校集合
            Set<String> schools = new HashSet<>();
            maps.stream().filter(m -> m.get("省份").toString().equals(province)).forEach(m -> schools.add(m.get("学校").toString()));
            //判断是否显示排名
            boolean flag = true;
            if (schools.size()<=schoolThreshold || objs.size()<=provinceThreshold){
                flag = false;
            }
            System.out.println("学校数量:"+ schools.size()+"; 省份数量:"+objs.size());
            for (Map<String, Object> obj : objs) {
                if (!flag){
                    obj.put("本省排名","-");
                }else if (flag && Long.parseLong(obj.get("本省排名").toString())<50l){
                    obj.put("本省排名","本省前50");
                }
            }
            writer.write(objs);
            writer.close();
        }
    }


    public void judgmentiSExit(String filePath){
        File file = new File(filePath);
        if (file.exists()){
            createToTsingHuaExcelFeiGaiGe(filePath);
        }
    }

    /**
     * 返回InsertQuesErrErr
     *
     */
    public void insertHaiXiaoErr(String msg) {
        RedisErr redisErr = new RedisErr();
        redisErr.setMsg(msg);
        redisErr.setCurrentTime(DateUtil.now());
        JSON parse = JSONUtil.parse(redisErr);
        redisTemplate.opsForSet().add(examUUID+"DataReprocessingByHaiXiaoErr",parse);
    }

    /**
     * 多线程之填空插入穷举表资源类
     *
     */
    private class MultithreadingOfWorkData implements Runnable {
        private CountDownLatch threadsSignal;
        public MultithreadingOfWorkData(CountDownLatch threadsSignal) {
            this.threadsSignal = threadsSignal;
        }

        @Override
        public void run() {
            List<Object[]> objects = new CopyOnWriteArrayList<>();
            for (int i = 0; i < 100; i++) {
                if (importScoreExcelQueue.isEmpty()){
                    break;
                }
                Map<String, Object> vo = importScoreExcelQueue.poll();//消费
                //取出各题分数塞进新的队列
                //最后只需要 zkz_num subject_relation_id ques_id ques_index score 即可
                Object[] objectArr = new Object[16];
                int num = 0;
                for (Map.Entry<String, Object> entry : vo.entrySet()) {
                    if (entry.getKey().equals("选考科目") && StrUtil.isNotBlank(entry.getValue().toString())){
                        changeCategory(entry);
                    }
                    Object value = entry.getValue();
                    if (StrUtil.isBlank(value.toString()) && !entry.getKey().equals("选考科目")){
                        objectArr[num] = null;
                    }else{
                        objectArr[num] = value;
                    }
                    num++;
                }
                objects.add(objectArr);
            }
            log.info("开始线程："+threadsSignal.getCount());
            jdbcTemplate.batchUpdate(insertSql,objects);
            // 线程结束时计数器减1
            threadsSignal.countDown();//必须等核心处理逻辑处理完成后才可以减1
            log.info(Thread.currentThread().getName() + "结束. 还有"+ threadsSignal.getCount() + " 个线程 ");
        }
    }

    public void changeCategory(Map.Entry<String, Object> entry){
        String category = entry.getValue().toString();
        String categoryRe = category.replace("物", "物理+").replace("化", "化学+").replace("生", "生物+").replace("政", "政治+").replace("历", "历史+").replace("地", "地理+");
        String categorySub = categoryRe.substring(0, categoryRe.length() - 1);
        entry.setValue(categorySub);
    }

    //插入语句
    private static final String insertSql = "INSERT into zf_qgj1_zonghe_haixiao (province,school_name,banji,zkz_num,name,type,category,yuwen,shuxue,yingyu,wuli,huaxue,shengwu,zhengzhi,lishi,dili) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    private static final String createSql = "CREATE TABLE IF NOT EXISTS `zf_qgj1_zonghe_haixiao` (\n" +
            "  `province` varchar(50) DEFAULT NULL,\n" +
            "  `school_name` varchar(50) DEFAULT NULL,\n" +
            "  `banji` varchar(50) DEFAULT NULL,\n" +
            "  `zkz_num` varchar(50) DEFAULT NULL,\n" +
            "  `name` varchar(50) DEFAULT NULL,\n" +
            "  `type` varchar(50) DEFAULT NULL,\n" +
            "  `category` varchar(50) DEFAULT NULL,\n" +
            "  `category_desc` varchar(255) DEFAULT NULL,\n" +
            "  `yuwen` varchar(50) DEFAULT NULL,\n" +
            "  `shuxue` varchar(50) DEFAULT NULL,\n" +
            "  `yingyu` varchar(50) DEFAULT NULL,\n" +
            "  `wuli` varchar(50) DEFAULT NULL,\n" +
            "  `huaxue` varchar(50) DEFAULT NULL,\n" +
            "  `shengwu` varchar(50) DEFAULT NULL,\n" +
            "  `zhengzhi` varchar(50) DEFAULT NULL,\n" +
            "  `lishi` varchar(50) DEFAULT NULL,\n" +
            "  `dili` varchar(50) DEFAULT NULL,\n" +
            "  `sum_score` varchar(50) DEFAULT NULL,\n" +
            "  `xuanke_score` varchar(50) DEFAULT NULL,\n" +
            "  `sum_percentage_g` varchar(50) DEFAULT NULL,\n" +
            "  `sum_percentage_s` varchar(50) DEFAULT NULL,\n" +
            "  `sum_rank_x` varchar(50) DEFAULT NULL,\n" +
            "  `xuanke_percentage_g` varchar(50) DEFAULT NULL,\n" +
            "  `xuanke_percentage_s` varchar(50) DEFAULT NULL,\n" +
            "  `xuanke_rank_x` varchar(50) DEFAULT NULL,\n" +
            "  KEY `zkz_num` (`zkz_num`) USING BTREE\n" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    private static final String updateSumPercentageG = "UPDATE zf_qgj1_zonghe_haixiao a ,\n" +
            "(SELECT * from \n" +
            "(\n" +
            "SELECT \n" +
            "a.zkzNum as zkzNum,(a.sum_score + 0) as sum_score ,\n" +
            "@incr_row_rank := @incr_row_rank + 1 AS incr_row_rank,   \n" +
            "CASE\n" +
            "WHEN @pref_row_score  = (a.sum_score + 0) THEN @curr_row_rank\n" +
            "WHEN @pref_row_score := (a.sum_score + 0) THEN @curr_row_rank := @incr_row_rank\n" +
            "WHEN @pref_row_score  = 0     THEN @curr_row_rank := @incr_row_rank\n" +
            "END AS `rank`,\n" +
            "@curr_row_rank AS curr_row_rank,\n" +
            "@pref_row_score AS pref_row_score,\n" +
            "c.count,\n" +
            "CONVERT(((@curr_row_rank - 1 + c.count*0.5)*100/d.zs),DECIMAL(18,1)) as baifenbi\n" +
            "from\n" +
            "(SELECT zkz_num as zkzNum, (sum_score + 0) as sum_score FROM zf_qgj1_zonghe_haixiao\n" +
            "where sum_score != 0\n" +
            "ORDER BY (sum_score + 0)) a,\n" +
            "(SELECT \n" +
            "@curr_row_rank := 0, \n" +
            "@pref_row_score := NULL,\n" +
            "@incr_row_rank := 0\n" +
            ") vars,\n" +
            "(SELECT COUNT(sum_score + 0) count,(sum_score + 0) sum_score from zf_qgj1_zonghe_haixiao GROUP BY (sum_score + 0)) c,\n" +
            "(SELECT COUNT(1) zs from zf_qgj1_zonghe_haixiao) d\n" +
            "WHERE a.sum_score = c.sum_score\n" +
            "ORDER BY\n" +
            "(a.sum_score + 0) ASC\n" +
            ") a) b set a.sum_percentage_g = b.baifenbi\n" +
            "where a.zkz_num = b.zkzNum and a.sum_score = b.sum_score";

    private static final String updateSumPercentageS = "UPDATE zf_qgj1_zonghe_haixiao a ,\n" +
            "(\n" +
            "SELECT \n" +
            "a.zkzNum as zkzNum,(a.sum_score + 0) as sum_score ,\n" +
            "@incr_row_rank := @incr_row_rank + 1 AS incr_row_rank,   \n" +
            "CASE\n" +
            "WHEN @pref_row_score  = (a.sum_score + 0) THEN @curr_row_rank\n" +
            "WHEN @pref_row_score := (a.sum_score + 0) THEN @curr_row_rank := @incr_row_rank\n" +
            "WHEN @pref_row_score  = 0     THEN @curr_row_rank := @incr_row_rank\n" +
            "END AS `rank`,\n" +
            "@curr_row_rank AS curr_row_rank,\n" +
            "@pref_row_score AS pref_row_score,\n" +
            "c.count,\n" +
            "CONVERT(((@curr_row_rank - 1 + c.count*0.5)*100/d.zs),DECIMAL(18,1)) as baifenbi\n" +
            "from\n" +
            "(SELECT zkz_num as zkzNum, (sum_score + 0) as sum_score FROM zf_qgj1_zonghe_haixiao\n" +
            "where sum_score != 0 and province = ?\n" +
            "ORDER BY (sum_score + 0)) a,\n" +
            "(SELECT \n" +
            "@curr_row_rank := 0, \n" +
            "@pref_row_score := NULL,\n" +
            "@incr_row_rank := 0\n" +
            ") vars,\n" +
            "(SELECT COUNT(sum_score + 0) count,(sum_score + 0) sum_score from zf_qgj1_zonghe_haixiao where province = ?  GROUP BY (sum_score + 0)) c,\n" +
            "(SELECT COUNT(1) zs from zf_qgj1_zonghe_haixiao where province = ?) d\n" +
            "WHERE a.sum_score = c.sum_score\n" +
            "ORDER BY\n" +
            "(a.sum_score + 0) ASC\n" +
            ") b set a.sum_percentage_s = b.baifenbi \n" +
            "where a.zkz_num = b.zkzNum";

    private static final String updateSumRankX = "UPDATE zf_qgj1_zonghe_haixiao a INNER JOIN\n" +
            "(\n" +
            "SELECT zkz_num,  sum_score,school_name, rank() OVER(order by sum_score+0 desc) as `rank` from zf_qgj1_zonghe_haixiao where  school_name = ? and sum_score!=0 \n" +
            ") b\n" +
            "ON a.sum_score =b.sum_score AND  a.zkz_num = b.zkz_num\n" +
            "SET a.sum_rank_x = b.`rank`  WHERE a.school_name = b.school_name ";

    private static final String updateXuankeRankX = "UPDATE zf_qgj1_zonghe_haixiao a INNER JOIN\n" +
            "(\n" +
            "SELECT zkz_num,  xuanke_score,school_name, rank() OVER(order by xuanke_score+0 desc) as `rank` from zf_qgj1_zonghe_haixiao where  school_name = ? and category = ? and xuanke_score != 0  \n" +
            ") b\n" +
            "ON a.xuanke_score =b.xuanke_score AND  a.zkz_num = b.zkz_num\n" +
            "SET a.xuanke_rank_x = b.`rank`  WHERE a.school_name = b.school_name ";

    private static final String updateXuankePercentageG = "UPDATE zf_qgj1_zonghe_haixiao a ,\n" +
            "(\n" +
            "SELECT \n" +
            "a.zkzNum as zkzNum,(a.xuanke_score + 0) as xuanke_score ,\n" +
            "@incr_row_rank := @incr_row_rank + 1 AS incr_row_rank,  \n" +
            "CASE\n" +
            "WHEN @pref_row_score  = (a.xuanke_score + 0) THEN @curr_row_rank\n" +
            "WHEN @pref_row_score := (a.xuanke_score + 0) THEN @curr_row_rank := @incr_row_rank\n" +
            "WHEN @pref_row_score  = 0     THEN @curr_row_rank := @incr_row_rank\n" +
            "END AS `rank`,\n" +
            "@curr_row_rank AS curr_row_rank,\n" +
            "@pref_row_score AS pref_row_score,\n" +
            "c.count,\n" +
            "CONVERT(((@curr_row_rank - 1 + c.count*0.5)*100/d.zs),DECIMAL(18,1)) as baifenbi\n" +
            "from\n" +
            "(SELECT zkz_num as zkzNum, (xuanke_score + 0) as xuanke_score FROM zf_qgj1_zonghe_haixiao\n" +
            "where xuanke_score != 0 and category = ?\n" +
            "ORDER BY (xuanke_score + 0)) a,\n" +
            "(SELECT \n" +
            "@curr_row_rank := 0, \n" +
            "@pref_row_score := NULL,\n" +
            "@incr_row_rank := 0\n" +
            ") vars,\n" +
            "(SELECT COUNT(xuanke_score + 0) count,(xuanke_score + 0) xuanke_score from zf_qgj1_zonghe_haixiao where category = ? GROUP BY (xuanke_score + 0)) c,\n" +
            "(SELECT COUNT(1) zs from zf_qgj1_zonghe_haixiao where category = ?) d\n" +
            "WHERE a.xuanke_score = c.xuanke_score\n" +
            "ORDER BY\n" +
            "(a.xuanke_score + 0) ASC" +
            ") b set a.xuanke_percentage_g = b.baifenbi \n" +
            "where a.zkz_num = b.zkzNum";

    private static final String updateXuankePercentageS = "UPDATE zf_qgj1_zonghe_haixiao a ,\n" +
            "(\n" +
            "SELECT \n" +
            "a.zkzNum as zkzNum,(a.xuanke_score + 0) as xuanke_score ,\n" +
            "@incr_row_rank := @incr_row_rank + 1 AS incr_row_rank,  \n" +
            "CASE\n" +
            "WHEN @pref_row_score  = (a.xuanke_score + 0) THEN @curr_row_rank\n" +
            "WHEN @pref_row_score := (a.xuanke_score + 0) THEN @curr_row_rank := @incr_row_rank\n" +
            "WHEN @pref_row_score  = 0     THEN @curr_row_rank := @incr_row_rank\n" +
            "END AS `rank`,\n" +
            "@curr_row_rank AS curr_row_rank,\n" +
            "@pref_row_score AS pref_row_score,\n" +
            "c.count,\n" +
            "CONVERT(((@curr_row_rank - 1 + c.count*0.5)*100/d.zs),DECIMAL(18,1)) as baifenbi\n" +
            "from\n" +
            "(SELECT zkz_num as zkzNum, (xuanke_score + 0) as xuanke_score FROM zf_qgj1_zonghe_haixiao\n" +
            "where xuanke_score != 0 and category = ? and province = ?\n" +
            "ORDER BY (xuanke_score + 0)) a,\n" +
            "(SELECT \n" +
            "@curr_row_rank := 0, \n" +
            "@pref_row_score := NULL,\n" +
            "@incr_row_rank := 0\n" +
            ") vars,\n" +
            "(SELECT COUNT(xuanke_score + 0) count,(xuanke_score + 0) xuanke_score from zf_qgj1_zonghe_haixiao where category = ? and province = ? GROUP BY (xuanke_score + 0)) c,\n" +
            "(SELECT COUNT(1) zs from zf_qgj1_zonghe_haixiao where category = ? and province = ?) d\n" +
            "WHERE a.xuanke_score = c.xuanke_score\n" +
            "ORDER BY\n" +
            "(a.xuanke_score + 0) ASC" +
            ") b set a.xuanke_percentage_s = b.baifenbi \n" +
            "where a.zkz_num = b.zkzNum";

    private static final String queryJiaShiData = "SELECT DISTINCT\n" +
            "d.province_name province,\n" +
            "d.NAME school_name,\n" +
            "f.NAME name,\n" +
            "t.* \n" +
            "FROM\n" +
            "(\n" +
            "SELECT\n" +
            "a.zkz_num,\n" +
            "a.school_id,\n" +
            "MAX( CASE subject_relation_id WHEN '1013' THEN a.score ELSE '' END ) scoreOne,\n" +
            "MAX( CASE subject_relation_id WHEN '1013' THEN a.percentage_g ELSE '' END ) rankOneG,\n" +
            "MAX( CASE subject_relation_id WHEN '1013' THEN a.percentage_s ELSE '' END ) rankOneS,\n" +
            "MAX( CASE subject_relation_id WHEN '1014' THEN a.score ELSE '' END ) scoreTwo,\n" +
            "MAX( CASE subject_relation_id WHEN '1014' THEN a.percentage_g ELSE '' END ) rankTwoG,\n" +
            "MAX( CASE subject_relation_id WHEN '1014' THEN a.percentage_s ELSE '' END ) rankTwoS\n" +
            "FROM\n" +
            "dp_subject_score a \n" +
            "WHERE\n" +
            "province_id = ? \n" +
            "GROUP BY\n" +
            "zkz_num \n" +
            ") t\n" +
            "LEFT JOIN dp_total_score b ON t.zkz_num = b.zkz_num\n" +
            "LEFT JOIN sparta_school d ON d.id = t.school_id\n" +
            "LEFT JOIN excel_student2019 f ON f.id_card = b.sfz_num \n" +
            "where (scoreOne != '' or scoreTwo != '')\n" +
            "ORDER BY\n" +
            "b.score + 0 DESC";
}
